import pymysql
import xlrd

#
# # 1. 循环输入10个人的信息（姓名，年龄，性别，身高），并存储到数据库中
# # 前提操作 mysql
# # 创建数据库test4
# #  create database test4;
# # use test4;
# # 选择test4数据库
# # 创建表person
# '''
# create table person
# (
# 	useId int primary key auto_increment,
# 	userName varchar(20) not null,
# 	age int(3) not null,
# 	sex char(4) not null,
# 	high int not null
# )engine=innodb default charset=utf8;
# '''
#
# connect = pymysql.connect(host='localhost', user='root', passwd='sxx123', database='test4', port=3306)
#
# cursor = connect.cursor()
# # 创建游标对象
#
# arr = [
#     ['liuze', 23, '男', 167],
#     ['liuze', 23, '男', 167],
#     ['liuze', 23, '男', 167],
#     ['liuze', 23, '男', 167],
#     ['liuze', 23, '男', 167],
#     ['liuze', 23, '男', 167],
#     ['liuze', 23, '男', 167],
#     ['liuze', 23, '男', 167],
#     ['liuze', 23, '男', 167],
#     ['liuze', 23, '男', 167],
# ]
#
# for e in arr:
#     userName = e[0]
#     age = e[1]
#     sex = e[2]
#     high = e[3]
#     cursor.execute(f"insert into person values(null,'{userName}',{age},'{sex}',{high})")
#
# connect.commit()
# # 提交
# cursor.close()
# connect.close()



# 2.


connect = pymysql.connect(host='localhost', user='root', passwd='sxx123', database='test4', port=3306)

cursor = connect.cursor()

xls = xlrd.open_workbook(filename='./datas/1.xls')

sheet = xls.sheets()[0]

rows = sheet.nrows
sql = '''
    insert into user_info 
    values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
'''
arr = []
for i in range(1,rows):
    data = sheet.row_values(i)
    arr.append(tuple(data))

cursor.executemany(sql,arr)

connect.commit()

cursor.close()

connect.close()


